package servlet.books;

import com.alibaba.fastjson.JSONObject;
import po.BookManage;
import utils.JdbcUtil;
import utils.ResultData;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 图书信息显示
 */
@WebServlet(urlPatterns = "/booksList")
public class BooksList extends HttpServlet {
    JdbcUtil jdbcUtil = new JdbcUtil();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        //获取当前页和条数
        Integer page = Integer.valueOf(req.getParameter("page"));
        Integer limit = Integer.valueOf(req.getParameter("limit"));
        Integer current = (page - 1) * limit;
        //获取带条件搜索的值
        String no = req.getParameter("no");
        String name = req.getParameter("name");
        String type = req.getParameter("type");
        //打开jdbc连接
        Connection connection = jdbcUtil.getConnection();
        //查询语句
        String sql = "select * from book_manage where 1 = 1";
        try {
            //动态sql拼接
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append(sql);
            //记录当前占位符的顺序
            int i = 0;
            if(no!=null&&no.trim().length()!= 0) {
                stringBuffer.append(" and no like ?");
            }
            if(name!=null&&name.trim().length()!=0) {
                stringBuffer.append(" and name like ?");
            }
            if(type!=null&&type.trim().length()!= 0) {
                stringBuffer.append(" and type = ?");
            }

            PreparedStatement preparedStatement = connection.prepareStatement(stringBuffer.toString());
            //拼接参数
            if (no!= null && no.trim().length() != 0) {
                ++i;
                preparedStatement.setString(i, "%"+no+"%");
            }
            if (name != null && name.trim().length() != 0) {
                ++i;
                preparedStatement.setString(i, "%"+name+"%");
            }
            if (type != null && type.trim().length() != 0) {
                ++i;
                preparedStatement.setString(i, type);
            }
            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            //光标最后一位
            resultSet.last();
            //获取总数据条数
            Integer count = resultSet.getRow();
            //分页的sql拼接
            stringBuffer.append(" limit ?,?");
            preparedStatement = connection.prepareStatement(stringBuffer.toString());
            //设置分页的参数的占位符
            i = 0;
            if (no!= null && no.trim().length() != 0) {
                ++i;
                preparedStatement.setString(i, "%"+no+"%");
            }
            if (name!=null&&name.trim().length()!=0) {
                ++i;
                preparedStatement.setString(i, "%"+name+"%");
            }
            if (type!=null&&type.trim().length()!= 0) {
                ++i;
                preparedStatement.setString(i, type);
            }
            ++i;
            preparedStatement.setInt(i, current);
            ++i;
            preparedStatement.setInt(i, limit);
            //查询数据
            resultSet = preparedStatement.executeQuery();
            //封装数据
            List<BookManage> bookManageList = new ArrayList<>();
            while (resultSet.next()) {
                BookManage bookManage = new BookManage();
                bookManage.setId(resultSet.getInt("id"));
                bookManage.setNo(resultSet.getString("no"));
                bookManage.setName(resultSet.getString("name"));
                bookManage.setType(resultSet.getString("type"));
                bookManage.setAuthor(resultSet.getString("author"));
                bookManage.setPrice(resultSet.getInt("price"));
                bookManage.setLanguage(resultSet.getString("language"));
                bookManageList.add(bookManage);
            }
            //返回数据
            ResultData page1 = ResultData.page(bookManageList, count.longValue());
            Object o = JSONObject.toJSON(page1);
            resp.getWriter().print(o.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

